Meet NULL the UNKNOWN

Postgres Conf Europe, Milan
2019-10-16

Image by Gerd Altmann from Pixabay

Who am I

Image by Anemone123 from Pixabay

Meet NULL the UNKNOWN

  • What's NULL?
  • What's UNKNOWN?
  • What's a boolean?
  • What's the 3-valued logic ?
  • Why should I care?
Image by Gerd Altmann from Pixabay

Definitions

Image by PDPics from Pixabay

NULL

Special value that is used to indicate the absence of any data value
Every data type includes a special value, called the null value, sometimes denoted by the keyword NULL.
Image by Gerd Altmann from Pixabay
NULL is not
  • an empty string
  • a string with only spaces in it
  • the string 'NULL'
  • 0

UNKNOWN

Value of the Boolean data type is either true or false.
The truth value of unknown is sometimes represented by the null value.
So, for a boolean attribute there are 4 different values:
  • true
  • false
  • unknown
  • null
Image by David Mark from Pixabay
test=> create table test (test boolean);
CREATE TABLE
test=> insert into test values (true),(false),(unknown),(null)
;

2019-10-14 18:08:52.088 CEST [5688] ERROR:  column "unknown"
 does not exist at character 41

2019-10-14 18:08:52.088 CEST [5688] STATEMENT:  insert into
 test values (true),(false),(unknown),(null);
ERROR:  column "unknown" does not exist
LINE 1: insert into test values (true),(false),(unknown),
(null);
test=> insert into test values (true),(false),(null),
(null);
INSERT 0 4

test=> select * from test;
 test 
------
 t
 f
 
 
(4 rows)
Does PostgreSQL implement T031 ?
<boolean literal> ::=
    TRUE
  | FALSE
  | UNKNOWN
Image by Mandyme27 from Pixabay
This specification does not make a distinction between the null value of the boolean data type and the truth value Unknown that is the result of an SQL predicate, search condition, or boolean value expression; they may be used interchangeably to mean exactly the same thing.
Image by JamesDeMers from Pixabay

Displaying null

          test=# \pset null 'Ada Lovelace'
Null display is "Ada Lovelace".

test=# select * from test;
     test     
--------------
 t
 f
 Ada Lovelace
 Ada Lovelace
(4 rows)


test=# 
Image by rawpixel from Pixabay

3-value logic

          select coalesce(ros.a::text,'unknown') as and_truth_table,
ros.a and cols.a as t,
ros.a and cols.b as f,
ros.a and cols.c as unknown
from (values (true, false, null::boolean)) as cols (a,b,c),
  (values (true),(false),(null)) as ros (a)

3-value logic

           and_truth_table |      t       | f |   unknown    
-----------------+--------------+---+--------------
 true            | t            | f | Ada Lovelace
 false           | f            | f | f
 unknown         | Ada Lovelace | f | Ada Lovelace
(3 rows)

3-value logic

Image by Mike from http://spikedmath.com

It's time for a quiz!

Image by Shahid Abdullah from Pixabay:

How many rows will this query return ?

select a, b
from (values (1, true),
             (2, false),
             (3, null)) as t (a,b)
where null;

How many rows will this query return ?

select a, b
from (values (1, true), (2, false), (3, null)) as t (a,b)
where null;
  • 0
  • 1
  • 3
  • Aminata Sana Congo

How many rows will this query return ?

select a, b
from (values (1, true),
             (2, false),
             (3, null)) as t (a,b)
where null;
 a | b  
---+----
(0 rows)

How many rows will this query return ?

select a, b 
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b <> 'aa';

How many rows will this query return ?

select a, b 
from (values (1, 'aa'), (2, 'bb'), (3, null)) as t (a,b)
where t.b <> 'aa';
  • 0
  • 1
  • 2
  • Maria Mitchel

How many rows will this query return ?

select a, b 
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b <> 'aa';
 a | b  
---+----
 2 | bb
(1 row)

How many rows will this query return ?

select a, b 
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b is distinct from 'aa';

How many rows will this query return ?

select a, b 
from (values (1, 'aa'), (2, 'bb'), (3, null)) as t (a,b)
where t.b is distinct from 'aa';
  • 0
  • 1
  • 2
  • Nicole-Reine Étable de la Brière Lepaute

How many rows will this query return ?

select a, b 
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b is distinct from 'aa';
 a | b  
---+----
 2 | bb
 3 | 
(2 rows)

How many rows will this query return ?

select a, b
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b in ('aa',null);

How many rows will this query return ?

select a, b
from (values (1, 'aa'), (2, 'bb'), (3, null)) as t (a,b)
where t.b in ('aa',null);
  • 0
  • 1
  • 2
  • Annie Jump Cannon

How many rows will this query return ?

select a, b
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b in ('aa',null);
 a | b 
---+---
 1 | aa
(1 row)

How many rows will this query return ?

select a, b
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b not in ('aa',null);

How many rows will this query return ?

select a, b
from (values (1, 'aa'), (2, 'bb'), (3, null)) as t (a,b)
where t.b not in ('aa',null);
  • 0
  • 1
  • 2
  • Alice Lee

How many rows will this query return ?

select a, b
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) AS t (a,b)
where t.b not in ('aa',null);
 a | b 
---+---
(0 rows)

How many rows will this query return ?

select a, b
from (values (1),
             (2),
             (null)) as t (a,b)
where t.a between 1 and null;

How many rows will this query return ?

select a, b
from (values (1), (2), (null)) as t (a,b)
where t.a between 1 and null;
  • 0
  • 1
  • 2
  • Jaime Levy

How many rows will this query return ?

select a, b
from (values (1),
             (2),
             (null)) as t (a,b)
where t.a between 1 and null;
 a | b 
---+---
(0 rows)

How many rows will this query return?

select a, b
from (values (1, true),
             (2, false),
             (3, null)) as t (a,b)
where t.b < true;

How many rows will this query return?

select a, b
from (values (1, true), (2, false), (3, null)) as t (a,b)
where t.b < true;
  • 0
  • 1
  • 2
  • Commodore Grace M. Hopper

How many rows will this query return?

select a, b
from (values (1, true),
             (2, false),
             (3, null)) as t (a,b)
where t.b < true;
 a | b 
---+---
 2 | f
(1 row)

What will this query return ?

          select (null=1)
       or (1=1) as "Annie Easley";

What will this query return ?

          select (null=1) or (1=1) as "Annie Easley";
  • true
  • false
  • null
  • Annie Easley

What will this query return ?

          select (null=1)
       or (1=1) as "Annie Easley";
 Annie Easley 
--------------
t
(1 row)

What will this query return ?

          select null is null is null
       is null is null
       as "Margaret Hamilton";

What will this query return ?

          select null is null is null is null is null as "Margaret
Hamilton";
  • true
  • false
  • null
  • Margaret Hamilton

What will this query return ?

          select null is null is null
       is null is null
       as "Margaret Hamilton";
 Margaret Hamilton 
-------------------
 f
(1 row)

What will this query return ?

          select row(null) is null;

What will this query return ?

          select row(null) is null;
  • true
  • false
  • null
  • Radia Perlman

What will this query return ?

          select row(null) is null;
  ?column? 
----------
 t
(1 row)

What will this query return ?

          select row(row(null)) is null;

What will this query return ?

          select row(row(null)) is null;
  • true
  • false
  • null
  • Brenda Laurel

What will this query return ?

          select row(row(null)) is null;
  ?column? 
----------
 f
(1 row)

What will this query return ?

select nullif(null,1);

What will this query return ?

select nullif(null,1);
  • 0
  • 1
  • null
  • Ada Lovelace

What will this query return ?

select nullif(null,1);
    nullif    
--------------
 Ada Lovelace
(1 row)

What will this query return ?

select 'Mary Lou ' || a || 'Jepsen'
from (values (null)) as t (a);

What will this query return ?

select 'Mary Lou ' || a || 'Jepsen'
from (values (null)) as t (a);
  • 0
  • unknown
  • Mary Lou Jepsen
  • Ada Lovelace

What will this query return ?

select 'Mary Lou ' || a || 'Jepsen'
from (values (null)) as t (a);
  ?column?   
--------------
 Ada Lovelace
(1 row)
Null means "no data"
Image by Andrew Martin from Pixabay
Unknown is a boolean value for "unknown"
Image by Andrew Martin from Pixabay

Should we care?

  • Yes
  • No
  • null
Image by Pexels from Pixabay

Feedback

https://2019.pgconf.eu/f